package eqpkanban

import com.tsqc.sys.CtTemType
import com.tsqc.sys.RtTemCont

/**
 * @Program: eqpkanban
 * @Description: SQL初始化
 * @Author: 樊双峰
 * @Date: 2018-07-08 20:51
 */
class SQLInitUtil {

    def sqlInit() {
        // 获取SQL类型
        def contextTypeId = CtTemType.findByName("SQL")
        // 设备信息
        def eqpListSQL = new RtTemCont(name: "eqpList", contDesc: "eqpList", context: "SELECT T.EQP_ID EQP_ID FROM EQUIPMENT T WHERE T.EQP_ID IS NOT NULL GROUP BY T.EQP_ID ORDER BY T.EQP_ID", contextType: contextTypeId, dateCreated: new Date(), defParam: null, lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 图形信息
        def graphListSQL = new RtTemCont(name: "graphList", contDesc: "graphList", context: "SELECT T.GRAPH_NAME,T.GRAPH_DESC FROM GRAPH T WHERE T.GRAPH_NAME IS NOT NULL AND T.GRAPH_DESC IS NOT NULL GROUP BY T.GRAPH_NAME,T.GRAPH_DESC", contextType: contextTypeId, dateCreated: new Date(), defParam: null, lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 端口信息
        def portListSQL = new RtTemCont(name: "portList", contDesc: "portList", context: "SELECT T.PORT_ID FROM PORT T WHERE T.PORT_ID IS NOT NULL GROUP BY T.PORT_ID ORDER BY T.PORT_ID", contextType: contextTypeId, dateCreated: new Date(), defParam: null, lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 状态颜色
        def getStateColorSQL = new RtTemCont(name: "getStateColor", contDesc: "getStateColor", context: "SELECT T.STATE,T.COLOR FROM STATE_COLOR T WHERE T.STATE IS NOT NULL AND T.COLOR IS NOT NULL ORDER BY T.COLOR", contextType: contextTypeId, dateCreated: new Date(), defParam: null, lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 设备信息列表
        def getEqpInfoSQL = new RtTemCont(name: "getEqpInfo", contDesc: "getEqpInfo", context: "SELECT * FROM(SELECT T.*,ROWNUM RN FROM(SELECT E.ID,E.EQP_ID,G.GRAPH_DESC,E.X,E.Y,E.WIDTH,E.HEIGHT,E.ROTATION,EQ.EQP_DESC,EQ.EQP_STATE,EQ.EQP_COMM_STATE,EQ.EQP_TYPE,EQ.EQP_LOC,A.AREA_NAME,A.AREA_DESC FROM EQUIPMENT EQ,EQP_SHAPE E,GRAPH G,AREA A WHERE E.EQP_ID = EQ.EQP_ID AND G.GRAPH_NAME = E.GRAPH_NAME AND A.AREA_NAME = E.AREA_NAME <% if(isNotEmpty (eqpId)){%> AND E.EQP_ID IN '\$"+"{eqpId}'<%}%> <% if(isNotEmpty (areaName)){%> AND E.AREA_NAME IN '\$"+"{areaName}'<%}%> ORDER BY E.EQP_ID )T WHERE ROWNUM <= '\$"+"{endNo}')WHERE RN >= '\$"+"{startNo}'", contextType: contextTypeId, dateCreated: new Date(), defParam: "[{'NAME':'startNo','TYPE':'INTEGER','OPTIONS':true},{'NAME':'endNo','TYPE':'INTEGER','OPTIONS':true},{'NAME':'eqpId','TYPE':'STRING','OPTIONS':true},{'NAME':'areaName','TYPE':'STRING','OPTIONS':true}]", lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 设备条数
        def eqpCountSQL = new RtTemCont(name: "eqpCount", contDesc: "eqpCount", context: "SELECT COUNT(*) totalCount FROM(SELECT E.ID,E.EQP_ID,G.GRAPH_DESC,E.X,E.Y,E.WIDTH,E.HEIGHT,E.ROTATION,EQ.EQP_DESC,EQ.EQP_STATE,EQ.EQP_COMM_STATE,EQ.EQP_TYPE,EQ.EQP_LOC FROM EQUIPMENT EQ,EQP_SHAPE E,GRAPH G WHERE E.EQP_ID = EQ.EQP_ID AND G.GRAPH_NAME = E.GRAPH_NAME <% if(isNotEmpty (eqpId)) { %> AND E.EQP_ID IN '\$"+"{eqpId}' <% } %> )T", contextType: contextTypeId, dateCreated: new Date(), defParam: "[{'NAME':'eqpId','TYPE':'STRING','OPTIONS':true}]", lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 设备信息JSON
        def getEqpInfoJsonSQL = new RtTemCont(name: "getEqpInfoJson", contDesc: "getEqpInfoJson", context: "SELECT E.ID,E.EQP_ID,G.GRAPH_NAME EQP_GRAPH_NAME,E.X EQP_X,E.Y EQP_Y,E.WIDTH EQP_WIDTH,E.HEIGHT EQP_HEIGHT,E.ROTATION EQP_ROTATION,EQ.EQP_DESC,NVL(DECODE(EQ.EQP_COMM_STATE,'ONLINE',EQ.EQP_STATE,EQ.EQP_COMM_STATE),EQ.EQP_STATE) EQP_STATE,EQ.EQP_COMM_STATE,EQ.EQP_TYPE,EQ.EQP_LOC,S.COLOR EQP_COLOR FROM EQUIPMENT EQ,EQP_SHAPE E,GRAPH G,STATE_COLOR S WHERE E.EQP_ID = EQ.EQP_ID AND G.GRAPH_NAME = E.GRAPH_NAME AND S.STATE = NVL(DECODE(EQ.EQP_COMM_STATE,'ONLINE',EQ.EQP_STATE,EQ.EQP_COMM_STATE),EQ.EQP_STATE)", contextType: contextTypeId, dateCreated: new Date(), defParam: null, lastUpdated: new Date(), lmUser: "ADMIN", source: null)
        // 区域
        def areaListSQL = new RtTemCont(name: "areaList", contDesc: "areaList", context: "SELECT AREA_NAME,AREA_DESC FROM AREA WHERE AREA_NAME IS NOT NULL AND AREA_DESC IS NOT NULL GROUP BY AREA_NAME,AREA_DESC ORDER BY AREA_NAME", contextType: contextTypeId, dateCreated: new Date(), defParam: null, lastUpdated: new Date(), lmUser: "ADMIN", source: null)

        def eqpList = "eqpList"
        def graphList = "graphList"
        def portList = "portList"
        def getStateColor = "getStateColor"
        def getEqpInfo = "getEqpInfo"
        def eqpCount = "eqpCount"
        def getEqpInfoJson = "getEqpInfoJson"
        def areaList = "areaList"

        if ((RtTemCont.list().name).contains(eqpList) == false) {
            eqpListSQL.save()
        } else if ((RtTemCont.list().name).contains(graphList) == false) {
            graphListSQL.save()
        } else if ((RtTemCont.list().name).contains(portList) == false) {
            portListSQL.save()
        } else if ((RtTemCont.list().name).contains(getStateColor) == false) {
            getStateColorSQL.save()
        } else if ((RtTemCont.list().name).contains(getEqpInfo) == false) {
            getEqpInfoSQL.save()
        } else if ((RtTemCont.list().name).contains(eqpCount) == false) {
            eqpCountSQL.save()
        } else if ((RtTemCont.list().name).contains(getEqpInfoJson) == false) {
            getEqpInfoJsonSQL.save()
        } else if ((RtTemCont.list().name).contains(areaList) == false) {
            areaListSQL.save()
        }
    }
}